DAY13:實現數據分頁

2024 iThome 鐵人賽

DAY 13

Python

Python自修系列第 13 篇

16th鐵人賽

frankfrank8785

2024-08-16 12:22:51

199 瀏覽

分享至

安裝flask-paginate

pip install flask-paginate

from flask import Flask, render_template, request, redirect, url_for, flash, session
from flask_sqlalchemy import SQLAlchemy
from flask_login import LoginManager, UserMixin, login_user, login_required, logout_user, current_user
from flask_paginate import Pagination, get_page_parameter
import pandas as pd
import os

app = Flask(__name__)
app.config['SECRET_KEY'] = 'your_secret_key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///users.db'
app.config['UPLOAD_FOLDER'] = 'uploads'

db = SQLAlchemy(app)
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'

# 用戶模型
class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(150), unique=True, nullable=False)
    password = db.Column(db.String(150), nullable=False)

# 上傳歷史模型
class UploadHistory(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(150), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    timestamp = db.Column(db.DateTime, nullable=False)

@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))

# 在應用上下文內創建數據庫
with app.app_context():
    db.create_all()

# 定義根路由
@app.route('/', methods=['GET', 'POST'])
@login_required
def index():
    if request.method == 'POST':
        if 'file' not in request.files:
            return redirect(request.url)
        file = request.files['file']
        if file.filename == '':
            return redirect(request.url)
        if file:
            filepath = os.path.join(app.config['UPLOAD_FOLDER'], file.filename)
            file.save(filepath)
            
            # 保存上傳歷史記錄
            new_upload = UploadHistory(filename=file.filename, user_id=current_user.id, timestamp=pd.Timestamp.now())
            db.session.add(new_upload)
            db.session.commit()

            # 保存文件路径供分页使用
            session['filepath'] = filepath

            return redirect(url_for('index'))

    # 如果已经上传文件，加载数据并分页显示
    filepath = session.get('filepath')
    if filepath:
        df = pd.read_csv(filepath)
        page = request.args.get(get_page_parameter(), type=int, default=1)
        per_page = 10
        offset = (page - 1) * per_page
        paginated_df = df[offset:offset + per_page]
        pagination = Pagination(page=page, total=len(df), per_page=per_page, css_framework='bootstrap4')
        
        return render_template('index.html', tables=[paginated_df.to_html(classes='data')], pagination=pagination)

    # 顯示上傳歷史記錄
    history = UploadHistory.query.filter_by(user_id=current_user.id).order_by(UploadHistory.timestamp.desc()).all()
    return render_template('index.html', history=history)

# 启动Flask服务
if __name__ == '__main__':
    app.run(debug=True)

index.html

<!DOCTYPE html>
<html lang="zh-Hant">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>PTT 文章分析結果</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
    <div class="container">
        <h1>PTT 文章數據分析結果</h1>

        <form action="/" method="post" enctype="multipart/form-data">
            <label for="file">上傳CSV文件:</label>
            <input type="file" name="file" id="file">
            <button type="submit">上傳並分析</button>
        </form>

        {% if tables %}
            <h2>分析結果</h2>
            {% for table in tables %}
                <div>{{ table|safe }}</div>
            {% endfor %}

            <div>
                {{ pagination.links }}
            </div>
        {% endif %}

        {% if history %}
            <h2>上傳歷史</h2>
            <ul>
                {% for record in history %}
                    <li>{{ record.timestamp }} - {{ record.filename }}</li>
                {% endfor %}
            </ul>
        {% endif %}
    </div>
</body>
</html>